package com.example.sass.jdbc.comment;

import cn.hutool.core.util.StrUtil;
import com.example.sass.jdbc.entity.DateBaseEntity;
import org.springframework.stereotype.Component;

import java.io.BufferedReader;
import java.io.File;
import java.io.FileReader;
import java.sql.*;

@Component
public class CreateDateBaseComment {

    public static String  scriptFilePath = "src\\main\\resources\\db\\createDB.sql"; // SQL脚本文件路径

    // MySQL数据库连接信息
    public static String m_url = "jdbc:mysql://localhost:3306/"; // 数据库服务器地址和端口
    public static String m_dbName = "test"; // 更改为您的数据库名称
    public static String m_user = "root"; // 更改为您的数据库用户名
    public static String m_password = "123456"; // 更改为您的数据库密码

    public boolean creatDB(DateBaseEntity dateBase) {

        if (StrUtil.isNotEmpty(dateBase.getDb_url())) m_url = dateBase.getDb_url();
        if (StrUtil.isNotEmpty(dateBase.getDb_dbName())) m_dbName = dateBase.getDb_dbName();
        if (StrUtil.isNotEmpty(dateBase.getDb_user())) m_user = dateBase.getDb_user();
        if (StrUtil.isNotEmpty(dateBase.getDb_password())) m_password = dateBase.getDb_password();


        // 获取SQL脚本文件的绝对路径
        String absoluteScriptFilePath = new File(scriptFilePath).getAbsolutePath();
        System.out.println("SQL脚本文件的绝对路径: " + absoluteScriptFilePath);

        Connection connection = null;

        try {
            // 尝试连接到MySQL服务器
            connection = DriverManager.getConnection(m_url, m_user, m_password);

            System.out.println("成功连接到MySQL服务器");

            // 检查数据库是否存在
            if (!databaseExists(connection, m_dbName)) {
                // 如果数据库不存在，创建它
                createDatabase(connection, m_dbName);

                //创建库的同时创建表
                // 执行SQL脚本文件以创建表
                executeSqlScript(connection, absoluteScriptFilePath,m_dbName);
            }

            // 连接到数据库
            connection = DriverManager.getConnection(m_url + m_dbName, m_user, m_password);
            System.out.println("成功连接到数据库");

            // 如果需要，您可以在此执行其他数据库操作

        } catch (SQLException e) {
            System.err.println("无法连接到MySQL服务器或处理数据库: " + e.getMessage());
            return false;
        } finally {
            if (connection != null) {
                try {
                    connection.close();
                    System.out.println("数据库连接已关闭");
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }
        return true;
    }

    private static void executeSqlScript(Connection connection, String scriptFilePath,String dbName) throws SQLException {
        System.out.println("正在执行SQL脚本文件: " + scriptFilePath); // 打印SQL文件的相对路径
        try {
            // 选择要使用的数据库
            Statement selectDbStatement = connection.createStatement();
            selectDbStatement.execute("USE "+dbName); // 请将 "your_database_name" 更改为实际的数据库名称

            BufferedReader reader = new BufferedReader(new FileReader(scriptFilePath));
            String line;
            StringBuilder sql = new StringBuilder();
            Statement statement = connection.createStatement();

            while ((line = reader.readLine()) != null) {
                sql.append(line);
                if (line.endsWith(";")) {
                    // 执行每个SQL语句
                    statement.execute(sql.toString());
                    sql.setLength(0); // 清空StringBuilder以准备下一个语句
                }
            }

            reader.close();
            System.out.println("成功执行SQL脚本文件");
        } catch (Exception e) {
            e.printStackTrace();
            System.err.println("执行SQL脚本文件时出现错误: " + e.getMessage());
        }
    }


    private static boolean databaseExists(Connection connection, String dbName) throws SQLException {
        // 检查数据库是否存在
        ResultSet resultSet = connection.getMetaData().getCatalogs();
        while (resultSet.next()) {
            if (resultSet.getString("TABLE_CAT").equalsIgnoreCase(dbName)) {
                return true;
            }
        }
        return false;
    }

    private static void createDatabase(Connection connection, String dbName) throws SQLException {
        // 创建数据库
        Statement statement = connection.createStatement();
        statement.executeUpdate("CREATE DATABASE " + dbName);
        System.out.println("成功创建数据库");
    }

}
